﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;


namespace DBLibrary
{
    public class PrcParam
    {
        public int type = 0;
        public string name = "";
        public int lenght = 0;
        public byte xprec = 0;
        public byte xscale = 0;
        public int isoutparam = 0;
        public object value = null;
    }

    public class DBProcedures
    {
        private static int TimeOutSec = 300;

        #region Common functions

        public static int execProcedure(string connectKey, string procedure, SortedList paramList, ref DataTable dt, ref string msg)
        {
            int rtn = -1;
            dt = new DataTable();
            SqlDataAdapter adapter = new SqlDataAdapter();
            SqlConnection conn = DBFactory.getConnect(connectKey);
            SqlCommand sqlCmd = new SqlCommand(procedure, conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandTimeout = 1800;
            try
            {
                for (int i = 0; i < paramList.Count; i++)
                {
                    PrcParam param = (PrcParam)paramList.GetByIndex(i);
                    switch (param.type)
                    {
                        case 34:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Image, param.lenght);
                            break;
                        case 48:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.TinyInt, param.lenght);
                            break;
                        case 52:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallInt, param.lenght);
                            break;
                        case 56:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Int, param.lenght);
                            break;
                        case 98:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 106:
                        case 108:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Decimal, param.lenght);
                            sqlCmd.Parameters[param.name].Precision = param.xprec;
                            sqlCmd.Parameters[param.name].Scale = param.xscale;
                            break;
                        case 127:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.BigInt, param.lenght);
                            break;
                        case 165:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarBinary, param.lenght);
                            break;
                        case 167:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 175:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Char, param.lenght);
                            break;
                        case 231:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NVarChar, param.lenght / 2);
                            break;
                        case 239:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NChar, param.lenght / 2);
                            break;
                        case 61:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.DateTime, param.lenght);
                            break;
                        case 58:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallDateTime, param.lenght);
                            break;
                        case 189:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Timestamp, param.lenght);
                            break;
                        case 60:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Money, param.lenght);
                            break;
                        case 122:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallMoney, param.lenght);
                            break;
                    }
                    if (param.isoutparam == 0)
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Input;
                    else
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Output;
                    sqlCmd.Parameters[param.name].Value = param.value;
                }
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                adapter.SelectCommand = sqlCmd;
                adapter.Fill(dt);
                dt.TableName = "table_name";
                rtn = 0;
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
                msg = e2.Message;
                rtn = -1;
                return rtn;
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error(ex);
                msg = ex.Message;
                rtn = -1;
                return rtn;
            }
            finally
            {
                adapter.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int execProcedure(string procedure, SortedList paramList, ref DataTable dt, ref string msg)
        {
            return execProcedure("Default", procedure, paramList, ref dt, ref msg);
        }

        public static int execProcedure(string connectKey, string procedure, SortedList paramList, AsyncCallback callback, ref string msg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect(connectKey);
            SqlCommand sqlCmd = new SqlCommand(procedure, conn);
            sqlCmd.CommandType = CommandType.StoredProcedure;
            sqlCmd.CommandTimeout = 1800;
            try
            {
                for (int i = 0; i < paramList.Count; i++)
                {
                    PrcParam param = (PrcParam)paramList.GetByIndex(i);
                    switch (param.type)
                    {
                        case 34:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Image, param.lenght);
                            break;
                        case 48:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.TinyInt, param.lenght);
                            break;
                        case 52:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallInt, param.lenght);
                            break;
                        case 56:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Int, param.lenght);
                            break;
                        case 98:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 106:
                        case 108:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Decimal, param.lenght);
                            sqlCmd.Parameters[param.name].Precision = param.xprec;
                            sqlCmd.Parameters[param.name].Scale = param.xscale;
                            break;
                        case 127:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.BigInt, param.lenght);
                            break;
                        case 165:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarBinary, param.lenght);
                            break;
                        case 167:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.VarChar, param.lenght);
                            break;
                        case 175:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Char, param.lenght);
                            break;
                        case 231:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NVarChar, param.lenght / 2);
                            break;
                        case 239:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.NChar, param.lenght / 2);
                            break;
                        case 61:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.DateTime, param.lenght);
                            break;
                        case 58:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallDateTime, param.lenght);
                            break;
                        case 189:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Timestamp, param.lenght);
                            break;
                        case 60:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.Money, param.lenght);
                            break;
                        case 122:
                            sqlCmd.Parameters.Add(param.name, SqlDbType.SmallMoney, param.lenght);
                            break;
                    }
                    if (param.isoutparam == 0)
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Input;
                    else
                        sqlCmd.Parameters[param.name].Direction = ParameterDirection.Output;
                    sqlCmd.Parameters[param.name].Value = param.value;
                }
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.BeginExecuteNonQuery(callback, sqlCmd);
                rtn = 0;
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
                msg = e2.Message;
                rtn = -1;
                return rtn;
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error(ex);
                msg = ex.Message;
                rtn = -1;
                return rtn;
            }
            finally
            {
                sqlCmd = null;
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int execProcedure(string procedure, SortedList paramList, AsyncCallback callback, ref string msg)
        {
            return execProcedure("Default", procedure, paramList, callback, ref msg);
        }

        #endregion

        public static int Sp_Booking_Acknowledge(long BookingID, long AcknowledgedBy, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_Acknowledge", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@BookingID"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingID"].Value = BookingID;
                sqlCmd.Parameters.Add("@AcknowledgedBy", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@AcknowledgedBy"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@AcknowledgedBy"].Value = AcknowledgedBy;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_Cancel(long BookingID, long CancelledBy, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_Cancel", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@BookingID"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingID"].Value = BookingID;
                sqlCmd.Parameters.Add("@CancelledBy", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@CancelledBy"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@CancelledBy"].Value = CancelledBy;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_Close(long BookingID, int NewCardCountConfirm, int ReplaceCardCountConfirm, int RepersonalizeCardCountConfirm, int TerminateCardCountConfirm, int Type3CardCountConfirm, string Remarks, long ClosedBy, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_Close", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@BookingID"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingID"].Value = BookingID;
                sqlCmd.Parameters.Add("@NewCardCountConfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@NewCardCountConfirm"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@NewCardCountConfirm"].Value = NewCardCountConfirm;
                sqlCmd.Parameters.Add("@ReplaceCardCountConfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@ReplaceCardCountConfirm"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@ReplaceCardCountConfirm"].Value = ReplaceCardCountConfirm;
                sqlCmd.Parameters.Add("@RepersonalizeCardCountConfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RepersonalizeCardCountConfirm"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@RepersonalizeCardCountConfirm"].Value = RepersonalizeCardCountConfirm;
                sqlCmd.Parameters.Add("@TerminateCardCountConfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@TerminateCardCountConfirm"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@TerminateCardCountConfirm"].Value = TerminateCardCountConfirm;
                sqlCmd.Parameters.Add("@Type3CardCountConfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Type3CardCountConfirm"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Type3CardCountConfirm"].Value = Type3CardCountConfirm;
                sqlCmd.Parameters.Add("@Remarks", SqlDbType.NVarChar, -1);
                sqlCmd.Parameters["@Remarks"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Remarks"].Value = Remarks;
                sqlCmd.Parameters.Add("@ClosedBy", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@ClosedBy"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@ClosedBy"].Value = ClosedBy;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_GetEndTimeSlotByBeginTimeSlot(DateTime BookingDate, string BeginTime, int NewCard, int ReplaceCard, int RepersonalizeCard, int TerminateCard, int Type3Card, ref int EndTimeSlotID, ref string EndTime, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_GetEndTimeSlotByBeginTimeSlot", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingDate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@BookingDate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingDate"].Value = BookingDate;
                sqlCmd.Parameters.Add("@BeginTime", SqlDbType.NVarChar, 5);
                sqlCmd.Parameters["@BeginTime"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BeginTime"].Value = BeginTime;
                sqlCmd.Parameters.Add("@NewCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@NewCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@NewCard"].Value = NewCard;
                sqlCmd.Parameters.Add("@ReplaceCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@ReplaceCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@ReplaceCard"].Value = ReplaceCard;
                sqlCmd.Parameters.Add("@RepersonalizeCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RepersonalizeCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@RepersonalizeCard"].Value = RepersonalizeCard;
                sqlCmd.Parameters.Add("@TerminateCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@TerminateCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@TerminateCard"].Value = TerminateCard;
                sqlCmd.Parameters.Add("@Type3Card", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Type3Card"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Type3Card"].Value = Type3Card;
                sqlCmd.Parameters.Add("@EndTimeSlotID", SqlDbType.Int, 4);
                sqlCmd.Parameters["@EndTimeSlotID"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@EndTime", SqlDbType.NVarChar, 5);
                sqlCmd.Parameters["@EndTime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                EndTimeSlotID = Convert.ToInt32(sqlCmd.Parameters["@EndTimeSlotID"].Value.ToString());
                EndTime = sqlCmd.Parameters["@EndTime"].Value.ToString();
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_GetFreeResourceByBookingDate(DateTime BookingDate, ref int FreeResource, ref int FreeResourceT3, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_GetFreeResourceByBookingDate", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingDate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@BookingDate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingDate"].Value = BookingDate;
                sqlCmd.Parameters.Add("@FreeResource", SqlDbType.Int, 4);
                sqlCmd.Parameters["@FreeResource"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@FreeResourceT3", SqlDbType.Int, 4);
                sqlCmd.Parameters["@FreeResourceT3"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                FreeResource = Convert.ToInt32(sqlCmd.Parameters["@FreeResource"].Value.ToString());
                FreeResourceT3 = Convert.ToInt32(sqlCmd.Parameters["@FreeResourceT3"].Value.ToString());
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_Locate(long id, ref string bookinguniqueid, ref DateTime bookingdate, ref string begintimeslot, ref string endtimeslot, ref int serialno, ref int newcardcount, ref int replacecardcount, ref int repersonalizecardcount, ref int terminatecardcount, ref int type3cardcount, ref int newcardcountconfirm, ref int replacecardcountconfirm, ref int repersonalizecardcountconfirm, ref int terminatecardcountconfirm, ref int type3cardcountconfirm, ref long bookingstatus, ref string remarks, ref long createdby, ref string createdname, ref string sitename, ref string orgname, ref DateTime createddatetime, ref long cancelleddby, ref DateTime cancelleddatetime, ref long submittedby, ref DateTime submitteddatetime, ref long rejectedby, ref DateTime rejecteddatetime, ref long ackby, ref DateTime ackdatetime, ref long closedby, ref DateTime closeddatetime, ref int result, ref string errormsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_Locate", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@id", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@id"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@id"].Value = id;
                sqlCmd.Parameters.Add("@bookinguniqueid", SqlDbType.NVarChar, 100);
                sqlCmd.Parameters["@bookinguniqueid"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@bookingdate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@bookingdate"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@begintimeslot", SqlDbType.NVarChar, 10);
                sqlCmd.Parameters["@begintimeslot"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@endtimeslot", SqlDbType.NVarChar, 10);
                sqlCmd.Parameters["@endtimeslot"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@serialno", SqlDbType.Int, 4);
                sqlCmd.Parameters["@serialno"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@newcardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@newcardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@replacecardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@replacecardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@repersonalizecardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@repersonalizecardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@terminatecardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@terminatecardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@type3cardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@type3cardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@newcardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@newcardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@replacecardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@replacecardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@repersonalizecardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@repersonalizecardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@terminatecardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@terminatecardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@type3cardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@type3cardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@bookingstatus", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@bookingstatus"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@remarks", SqlDbType.NVarChar, -1);
                sqlCmd.Parameters["@remarks"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createdby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@createdby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createdname", SqlDbType.NVarChar, 200);
                sqlCmd.Parameters["@createdname"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@sitename", SqlDbType.NVarChar, 200);
                sqlCmd.Parameters["@sitename"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@orgname", SqlDbType.NVarChar, 200);
                sqlCmd.Parameters["@orgname"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@createddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@cancelleddby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@cancelleddby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@cancelleddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@cancelleddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@submittedby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@submittedby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@submitteddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@submitteddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@rejectedby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@rejectedby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@rejecteddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@rejecteddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ackby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@ackby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ackdatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@ackdatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@closedby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@closedby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@closeddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@closeddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@errormsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@errormsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                bookinguniqueid = sqlCmd.Parameters["@bookinguniqueid"].Value.ToString();
                DateTime.TryParse(sqlCmd.Parameters["@bookingdate"].Value.ToString(), out bookingdate);
                begintimeslot = sqlCmd.Parameters["@begintimeslot"].Value.ToString();
                endtimeslot = sqlCmd.Parameters["@endtimeslot"].Value.ToString();
                serialno = Convert.ToInt32(sqlCmd.Parameters["@serialno"].Value.ToString());
                newcardcount = Convert.ToInt32(sqlCmd.Parameters["@newcardcount"].Value.ToString());
                replacecardcount = Convert.ToInt32(sqlCmd.Parameters["@replacecardcount"].Value.ToString());
                repersonalizecardcount = Convert.ToInt32(sqlCmd.Parameters["@repersonalizecardcount"].Value.ToString());
                terminatecardcount = Convert.ToInt32(sqlCmd.Parameters["@terminatecardcount"].Value.ToString());
                type3cardcount = Convert.ToInt32(sqlCmd.Parameters["@type3cardcount"].Value.ToString());
                newcardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@newcardcountconfirm"].Value.ToString());
                replacecardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@replacecardcountconfirm"].Value.ToString());
                repersonalizecardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@repersonalizecardcountconfirm"].Value.ToString());
                terminatecardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@terminatecardcountconfirm"].Value.ToString());
                type3cardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@type3cardcountconfirm"].Value.ToString());
                Int64.TryParse(sqlCmd.Parameters["@bookingstatus"].Value.ToString(), out bookingstatus);
                remarks = sqlCmd.Parameters["@remarks"].Value.ToString();
                Int64.TryParse(sqlCmd.Parameters["@createdby"].Value.ToString(), out createdby);
                createdname = sqlCmd.Parameters["@createdname"].Value.ToString();
                sitename = sqlCmd.Parameters["@sitename"].Value.ToString();
                orgname = sqlCmd.Parameters["@orgname"].Value.ToString();
                DateTime.TryParse(sqlCmd.Parameters["@createddatetime"].Value.ToString(), out createddatetime);
                Int64.TryParse(sqlCmd.Parameters["@cancelleddby"].Value.ToString(), out cancelleddby);
                DateTime.TryParse(sqlCmd.Parameters["@cancelleddatetime"].Value.ToString(), out cancelleddatetime);
                Int64.TryParse(sqlCmd.Parameters["@submittedby"].Value.ToString(), out submittedby);
                DateTime.TryParse(sqlCmd.Parameters["@submitteddatetime"].Value.ToString(), out submitteddatetime);
                Int64.TryParse(sqlCmd.Parameters["@rejectedby"].Value.ToString(), out rejectedby);
                DateTime.TryParse(sqlCmd.Parameters["@rejecteddatetime"].Value.ToString(), out rejecteddatetime);
                Int64.TryParse(sqlCmd.Parameters["@ackby"].Value.ToString(), out ackby);
                DateTime.TryParse(sqlCmd.Parameters["@ackdatetime"].Value.ToString(), out ackdatetime);
                Int64.TryParse(sqlCmd.Parameters["@closedby"].Value.ToString(), out closedby);
                DateTime.TryParse(sqlCmd.Parameters["@closeddatetime"].Value.ToString(), out closeddatetime);
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                errormsg = sqlCmd.Parameters["@errormsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                result = rtn;
                errormsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                result = rtn;
                errormsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_LocateByBookingUniqueID(ref long id, string bookinguniqueid, ref DateTime bookingdate, ref string begintimeslot, ref string endtimeslot, ref int serialno, ref int newcardcount, ref int replacecardcount, ref int repersonalizecardcount, ref int terminatecardcount, ref int type3cardcount, ref int newcardcountconfirm, ref int replacecardcountconfirm, ref int repersonalizecardcountconfirm, ref int terminatecardcountconfirm, ref int type3cardcountconfirm, ref long bookingstatus, ref string remarks, ref long createdby, ref string createdname, ref string sitename, ref string orgname, ref DateTime createddatetime, ref long cancelleddby, ref DateTime cancelleddatetime, ref long submittedby, ref DateTime submitteddatetime, ref long rejectedby, ref DateTime rejecteddatetime, ref long ackby, ref DateTime ackdatetime, ref long closedby, ref DateTime closeddatetime, ref int result, ref string errormsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_LocateByBookingUniqueID", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@id", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@id"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@bookinguniqueid", SqlDbType.NVarChar, 100);
                sqlCmd.Parameters["@bookinguniqueid"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@bookinguniqueid"].Value = bookinguniqueid;
                sqlCmd.Parameters.Add("@bookingdate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@bookingdate"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@begintimeslot", SqlDbType.NVarChar, 10);
                sqlCmd.Parameters["@begintimeslot"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@endtimeslot", SqlDbType.NVarChar, 10);
                sqlCmd.Parameters["@endtimeslot"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@serialno", SqlDbType.Int, 4);
                sqlCmd.Parameters["@serialno"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@newcardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@newcardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@replacecardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@replacecardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@repersonalizecardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@repersonalizecardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@terminatecardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@terminatecardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@type3cardcount", SqlDbType.Int, 4);
                sqlCmd.Parameters["@type3cardcount"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@newcardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@newcardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@replacecardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@replacecardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@repersonalizecardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@repersonalizecardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@terminatecardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@terminatecardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@type3cardcountconfirm", SqlDbType.Int, 4);
                sqlCmd.Parameters["@type3cardcountconfirm"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@bookingstatus", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@bookingstatus"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@remarks", SqlDbType.NVarChar, -1);
                sqlCmd.Parameters["@remarks"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createdby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@createdby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createdname", SqlDbType.NVarChar, 200);
                sqlCmd.Parameters["@createdname"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@sitename", SqlDbType.NVarChar, 200);
                sqlCmd.Parameters["@sitename"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@orgname", SqlDbType.NVarChar, 200);
                sqlCmd.Parameters["@orgname"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@createddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@cancelleddby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@cancelleddby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@cancelleddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@cancelleddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@submittedby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@submittedby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@submitteddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@submitteddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@rejectedby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@rejectedby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@rejecteddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@rejecteddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ackby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@ackby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ackdatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@ackdatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@closedby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@closedby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@closeddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@closeddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@errormsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@errormsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                Int64.TryParse(sqlCmd.Parameters["@id"].Value.ToString(), out id);
                DateTime.TryParse(sqlCmd.Parameters["@bookingdate"].Value.ToString(), out bookingdate);
                begintimeslot = sqlCmd.Parameters["@begintimeslot"].Value.ToString();
                endtimeslot = sqlCmd.Parameters["@endtimeslot"].Value.ToString();
                serialno = Convert.ToInt32(sqlCmd.Parameters["@serialno"].Value.ToString());
                newcardcount = Convert.ToInt32(sqlCmd.Parameters["@newcardcount"].Value.ToString());
                replacecardcount = Convert.ToInt32(sqlCmd.Parameters["@replacecardcount"].Value.ToString());
                repersonalizecardcount = Convert.ToInt32(sqlCmd.Parameters["@repersonalizecardcount"].Value.ToString());
                terminatecardcount = Convert.ToInt32(sqlCmd.Parameters["@terminatecardcount"].Value.ToString());
                type3cardcount = Convert.ToInt32(sqlCmd.Parameters["@type3cardcount"].Value.ToString());
                newcardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@newcardcountconfirm"].Value.ToString());
                replacecardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@replacecardcountconfirm"].Value.ToString());
                repersonalizecardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@repersonalizecardcountconfirm"].Value.ToString());
                terminatecardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@terminatecardcountconfirm"].Value.ToString());
                type3cardcountconfirm = Convert.ToInt32(sqlCmd.Parameters["@type3cardcountconfirm"].Value.ToString());
                Int64.TryParse(sqlCmd.Parameters["@bookingstatus"].Value.ToString(), out bookingstatus);
                remarks = sqlCmd.Parameters["@remarks"].Value.ToString();
                Int64.TryParse(sqlCmd.Parameters["@createdby"].Value.ToString(), out createdby);
                createdname = sqlCmd.Parameters["@createdname"].Value.ToString();
                sitename = sqlCmd.Parameters["@sitename"].Value.ToString();
                orgname = sqlCmd.Parameters["@orgname"].Value.ToString();
                DateTime.TryParse(sqlCmd.Parameters["@createddatetime"].Value.ToString(), out createddatetime);
                Int64.TryParse(sqlCmd.Parameters["@cancelleddby"].Value.ToString(), out cancelleddby);
                DateTime.TryParse(sqlCmd.Parameters["@cancelleddatetime"].Value.ToString(), out cancelleddatetime);
                Int64.TryParse(sqlCmd.Parameters["@submittedby"].Value.ToString(), out submittedby);
                DateTime.TryParse(sqlCmd.Parameters["@submitteddatetime"].Value.ToString(), out submitteddatetime);
                Int64.TryParse(sqlCmd.Parameters["@rejectedby"].Value.ToString(), out rejectedby);
                DateTime.TryParse(sqlCmd.Parameters["@rejecteddatetime"].Value.ToString(), out rejecteddatetime);
                Int64.TryParse(sqlCmd.Parameters["@ackby"].Value.ToString(), out ackby);
                DateTime.TryParse(sqlCmd.Parameters["@ackdatetime"].Value.ToString(), out ackdatetime);
                Int64.TryParse(sqlCmd.Parameters["@closedby"].Value.ToString(), out closedby);
                DateTime.TryParse(sqlCmd.Parameters["@closeddatetime"].Value.ToString(), out closeddatetime);
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                errormsg = sqlCmd.Parameters["@errormsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                result = rtn;
                errormsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                result = rtn;
                errormsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_Reject(long BookingID, long RejectedBy, string Remarks, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_Reject", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@BookingID"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingID"].Value = BookingID;
                sqlCmd.Parameters.Add("@RejectedBy", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@RejectedBy"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@RejectedBy"].Value = RejectedBy;
                sqlCmd.Parameters.Add("@Remarks", SqlDbType.NVarChar, -1);
                sqlCmd.Parameters["@Remarks"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Remarks"].Value = Remarks;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_Booking_Submit(DateTime BookingDate, string BeginTimeSlot, int NewCard, int ReplaceCard, int RepersonalizeCard, int TerminateCard, int Type3Card, long UserID, string Remarks, ref long BookingID, ref string BookingUniqueID, ref string EndTime, ref int Result, ref string ErrorMsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_Submit", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingDate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@BookingDate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingDate"].Value = BookingDate;
                sqlCmd.Parameters.Add("@BeginTimeSlot", SqlDbType.NVarChar, 5);
                sqlCmd.Parameters["@BeginTimeSlot"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BeginTimeSlot"].Value = BeginTimeSlot;
                sqlCmd.Parameters.Add("@NewCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@NewCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@NewCard"].Value = NewCard;
                sqlCmd.Parameters.Add("@ReplaceCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@ReplaceCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@ReplaceCard"].Value = ReplaceCard;
                sqlCmd.Parameters.Add("@RepersonalizeCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RepersonalizeCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@RepersonalizeCard"].Value = RepersonalizeCard;
                sqlCmd.Parameters.Add("@TerminateCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@TerminateCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@TerminateCard"].Value = TerminateCard;
                sqlCmd.Parameters.Add("@Type3Card", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Type3Card"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Type3Card"].Value = Type3Card;
                sqlCmd.Parameters.Add("@UserID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@UserID"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@UserID"].Value = UserID;
                sqlCmd.Parameters.Add("@Remarks", SqlDbType.NVarChar, -1);
                sqlCmd.Parameters["@Remarks"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Remarks"].Value = Remarks;
                sqlCmd.Parameters.Add("@BookingID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@BookingID"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@BookingUniqueID", SqlDbType.NVarChar, 50);
                sqlCmd.Parameters["@BookingUniqueID"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@EndTime", SqlDbType.NVarChar, 5);
                sqlCmd.Parameters["@EndTime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@Result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@ErrorMsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@ErrorMsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                Int64.TryParse(sqlCmd.Parameters["@BookingID"].Value.ToString(), out BookingID);
                BookingUniqueID = sqlCmd.Parameters["@BookingUniqueID"].Value.ToString();
                EndTime = sqlCmd.Parameters["@EndTime"].Value.ToString();
                Result = Convert.ToInt32(sqlCmd.Parameters["@Result"].Value.ToString());
                ErrorMsg = sqlCmd.Parameters["@ErrorMsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                Result = rtn;
                ErrorMsg = ex.Message;
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static void Sp_Booking_SelectbyStatus(long bookingstatus, long siteid, ref DataTable dt)
        {
            dt = new DataTable();
            SqlDataReader reader = null;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_SelectbyStatus", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@bookingstatus", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@bookingstatus"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@bookingstatus"].Value = bookingstatus;
                sqlCmd.Parameters.Add("@siteid", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@siteid"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@siteid"].Value = siteid;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                reader = sqlCmd.ExecuteReader();
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static void Sp_Booking_SelectSuggestInformationByBookingDate(DateTime BookingDate, int NewCard, int ReplaceCard, int RepersonalizeCard, int TerminateCard, int Type3Card, ref DataTable dt)
        {
            dt = new DataTable();
            SqlDataReader reader = null;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_SelectSuggestInformationByBookingDate", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingDate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@BookingDate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingDate"].Value = BookingDate;
                sqlCmd.Parameters.Add("@NewCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@NewCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@NewCard"].Value = NewCard;
                sqlCmd.Parameters.Add("@ReplaceCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@ReplaceCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@ReplaceCard"].Value = ReplaceCard;
                sqlCmd.Parameters.Add("@RepersonalizeCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RepersonalizeCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@RepersonalizeCard"].Value = RepersonalizeCard;
                sqlCmd.Parameters.Add("@TerminateCard", SqlDbType.Int, 4);
                sqlCmd.Parameters["@TerminateCard"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@TerminateCard"].Value = TerminateCard;
                sqlCmd.Parameters.Add("@Type3Card", SqlDbType.Int, 4);
                sqlCmd.Parameters["@Type3Card"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@Type3Card"].Value = Type3Card;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                reader = sqlCmd.ExecuteReader();
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }
        
        public static void Sp_BookingDetailsSelectByBookingID(long BookingID, ref DataTable dt)
        {
            dt = new DataTable();
            SqlDataReader reader = null;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_BookingDetailsSelectByBookingID", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingID", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@BookingID"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingID"].Value = BookingID;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                reader = sqlCmd.ExecuteReader();
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error(ex);
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static int Sp_BookingLockDate_Delete(DateTime lockdate, ref int result, ref string errormsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_BookingLockDate_Delete", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@lockdate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@lockdate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@lockdate"].Value = lockdate;
                sqlCmd.Parameters.Add("@result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@errormsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@errormsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                errormsg = sqlCmd.Parameters["@errormsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                result = rtn;
                errormsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                result = rtn;
                errormsg = ex.Message;
                EPPLog.Logger.Error("Execute Procedure 'Sp_BookingLockDate_Delete' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_BookingLockDate_Create(DateTime lockdate, int range, string reason, long createdby, ref int result, ref string errormsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_BookingLockDate_Create", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@lockdate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@lockdate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@lockdate"].Value = lockdate;
                sqlCmd.Parameters.Add("@range", SqlDbType.Int, 4);
                sqlCmd.Parameters["@range"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@range"].Value = range;
                sqlCmd.Parameters.Add("@reason", SqlDbType.NVarChar, 300);
                sqlCmd.Parameters["@reason"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@reason"].Value = reason;
                sqlCmd.Parameters.Add("@createdby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@createdby"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@createdby"].Value = createdby;
                sqlCmd.Parameters.Add("@result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@errormsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@errormsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                errormsg = sqlCmd.Parameters["@errormsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                result = rtn;
                errormsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                result = rtn;
                errormsg = ex.Message;
                EPPLog.Logger.Error("Execute Procedure 'Sp_BookingLockDate_Create' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_BookingLockDate_Locate(DateTime lockdate, ref int range, ref string reason, ref long createdby, ref DateTime createddatetime, ref int result, ref string errormsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_BookingLockDate_Locate", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@lockdate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@lockdate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@lockdate"].Value = lockdate;
                sqlCmd.Parameters.Add("@range", SqlDbType.Int, 4);
                sqlCmd.Parameters["@range"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@reason", SqlDbType.NVarChar, 600);
                sqlCmd.Parameters["@reason"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createdby", SqlDbType.BigInt, 8);
                sqlCmd.Parameters["@createdby"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@createddatetime", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@createddatetime"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@errormsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@errormsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                range = Convert.ToInt32(sqlCmd.Parameters["@range"].Value.ToString());
                reason = sqlCmd.Parameters["@reason"].Value.ToString();
                Int64.TryParse(sqlCmd.Parameters["@createdby"].Value.ToString(), out createdby);
                DateTime.TryParse(sqlCmd.Parameters["@createddatetime"].Value.ToString(), out createddatetime);
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                errormsg = sqlCmd.Parameters["@errormsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                result = rtn;
                errormsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                result = rtn;
                errormsg = ex.Message;
                EPPLog.Logger.Error("Execute Procedure 'Sp_BookingLockDate_Locate' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static int Sp_BookingLockDate_Modify(DateTime lockdate, int range, string reason, ref int result, ref string errormsg)
        {
            int rtn = -1;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_BookingLockDate_Modify", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@lockdate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@lockdate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@lockdate"].Value = lockdate;
                sqlCmd.Parameters.Add("@range", SqlDbType.Int, 4);
                sqlCmd.Parameters["@range"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@range"].Value = range;
                sqlCmd.Parameters.Add("@reason", SqlDbType.NVarChar, 300);
                sqlCmd.Parameters["@reason"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@reason"].Value = reason;
                sqlCmd.Parameters.Add("@result", SqlDbType.Int, 4);
                sqlCmd.Parameters["@result"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@errormsg", SqlDbType.NVarChar, 500);
                sqlCmd.Parameters["@errormsg"].Direction = ParameterDirection.Output;
                sqlCmd.Parameters.Add("@RETURN_VALUE", SqlDbType.Int, 4);
                sqlCmd.Parameters["@RETURN_VALUE"].Direction = ParameterDirection.ReturnValue;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                sqlCmd.ExecuteNonQuery();
                result = Convert.ToInt32(sqlCmd.Parameters["@result"].Value.ToString());
                errormsg = sqlCmd.Parameters["@errormsg"].Value.ToString();
                rtn = Convert.ToInt32(sqlCmd.Parameters["@RETURN_VALUE"].Value.ToString());
            }
            catch (TimeoutException e2)
            {
                rtn = -1;
                result = rtn;
                errormsg = e2.Message;
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                rtn = -1;
                result = rtn;
                errormsg = ex.Message;
                EPPLog.Logger.Error("Execute Procedure 'Sp_BookingLockDate_Modify' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
            return rtn;
        }

        public static void Sp_BookingLockDate_Query(ref DataTable dt)
        {
            dt = new DataTable();
            SqlDataReader reader = null;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_BookingLockDate_Query", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                reader = sqlCmd.ExecuteReader();
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error("Execute Procedure 'Sp_BookingLockDate_Query' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static void Sp_UserProfile_SelectBookingAdmin(ref DataTable dt)
        {
            dt = new DataTable();
            SqlDataReader reader = null;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_UserProfile_SelectBookingAdmin", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                reader = sqlCmd.ExecuteReader();
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error("Execute Procedure 'Sp_UserProfile_SelectBookingAdmin' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }

        public static void Sp_Booking_SelectTimeSlotByBookingDate(DateTime BookingDate, ref DataTable dt)
        {
            dt = new DataTable();
            SqlDataReader reader = null;
            SqlConnection conn = DBFactory.getConnect();
            SqlCommand sqlCmd = new SqlCommand("Sp_Booking_SelectTimeSlotByBookingDate", conn);
            sqlCmd.CommandTimeout = TimeOutSec;
            try
            {
                sqlCmd.CommandType = CommandType.StoredProcedure;
                sqlCmd.Parameters.Add("@BookingDate", SqlDbType.DateTime, 8);
                sqlCmd.Parameters["@BookingDate"].Direction = ParameterDirection.Input;
                sqlCmd.Parameters["@BookingDate"].Value = BookingDate;
                if (conn.State == ConnectionState.Closed)
                    conn.Open();
                reader = sqlCmd.ExecuteReader();
                if (reader != null)
                {
                    dt.Load(reader);
                }
            }
            catch (TimeoutException e2)
            {
                EPPLog.Logger.Error(e2);
            }
            catch (Exception ex)
            {
                EPPLog.Logger.Error("Execute Procedure 'Sp_Booking_SelectTimeSlotByBookingDate' Failed, Error Message : '" + ex.Message + "'.");
            }
            finally
            {
                if (reader != null)
                    reader.Dispose();
                sqlCmd.Dispose();
                if (conn != null)
                {
                    if (conn.State != ConnectionState.Closed)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }

    }
}
